ccd = (db2.query_to_table(cdb, """
select id_b,
sum(tot_crashes) tot_crashes,
sum(tot_fat) tot_fat,
sum(tot_inj) tot_inj,
sum(a) a,
sum(b) b,
sum(c) c,
sum(ksi) ksi,
sum(unk) unk,
sum(ped) ped,
sum(bike) bike
from (
--All Crash Details for Segments
select * from (
--All Block from Segemnts Within Studay Area
with data as(
select id_b, segmentid
from(select mft,segmentid, geom from lion
where mft in (select l.mft from lion l join working.study_area sa on l.segmentid = sa.segmentid)) ll
join(select sa.id_b, l.mft
from lion l join working.study_area sa
on l.segmentid = sa.segmentid
group by id_b, l.mft) id
on ll.mft = id.mft)
--Sum of all segment injuries joined with fatalies within study area grouped by id_b
select x.id_b,
count(case_num) tot_crashes,
coalesce(id_,0) tot_fat,
sum(num_of_inj) tot_inj ,
sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'A'::text, ''::text))) AS A,
sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'B'::text, ''::text))) AS B,
sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'C'::text, ''::text))) AS C,
ABS(sum((num_of_fat + num_of_inj)-length(TRIM(ext_of_inj::text)))) UNK,
coalesce(id_,0) + sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'A'::text, ''::text))) AS KSI,
sum(case when accd_type_int= 1 then num_of_inj else 0 end) as Ped,
sum(case when accd_type_int= 2 then num_of_inj else 0 end) as Bike
from(
select distinct id_b, case_num, ext_of_inj, crashid, num_of_fat, num_of_inj, accd_type_int, nys.loc
from (select * from nysdot_all where case_yr between 2005 and 2014 and exclude = 0) nys
join data on nys.segmentid = data.segmentid)x
left join(select id_b,count(id_) id_ from (select * from fatality_nycdot_current where yr between 2005 and 2014) f
join data on f.segmentid = data.segmentid group by id_b)y
on x.id_b = y.id_b
where x.id_b is not null
group by x.id_b, id_
) corr
union
--All Crash Details fOR INTERSECTIONS
select * from (
--All Intersections of All Blocks fom Segemnts Within Studay Area
with data as(
select mids.id_b,t.mid, t.nodeid
from(select nodeidfrom::int nodeid, masteridfrom mid from lion
where mft in (select l.mft from lion l join working.study_area sa on l.segmentid = sa.segmentid)
union
select nodeidto::int nodeid, masteridto mid from lion
where mft in (select l.mft from lion l join working.study_area sa on l.segmentid = sa.segmentid)) t
join(select id_b, l.masteridfrom mid
from lion l
join working.study_area sa on l.segmentid = sa.segmentid
union
select id_b, l.masteridto mid
from lion l
join working.study_area sa on l.segmentid = sa.segmentid) mids
on t.mid = mids.mid)
--Sum of all intersection injuries joined with fatalies within study area grouped by id_b
select x.id_b,
count(crashid) tot_crashes,
coalesce (id_,0) tot_fat,
sum(num_of_inj) tot_inj,
sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'A'::text, ''::text))) AS A,
sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'B'::text, ''::text))) AS B,
sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'C'::text, ''::text))) AS C,
coalesce(id_,0) + sum(length(ext_of_inj::text) - length(replace(ext_of_inj::text, 'A'::text, ''::text))) AS KSI,
ABS(sum((num_of_fat + num_of_inj)-length(TRIM(ext_of_inj::text)))) UNK,
sum(case when accd_type_int= 1 then num_of_inj else 0 end) as Ped,
sum(case when accd_type_int= 2 then num_of_inj else 0 end) as Bike
from(
select distinct id_b, case_num, ext_of_inj, crashid, num_of_inj, num_of_fat, accd_type_int, nys.loc
from (select * from nysdot_all where case_yr between 2005 and 2014 and exclude = 0) nys
join data on nys.masterid::int = data.mid) x
left join(select id_b, count(id_) id_ from (
select distinct id_b, id_ from (select * from fatality_nycdot_current where yr between 2005 and 2014) f
join data on f.nodeid::int = data.nodeid)ok group by id_b)y
on x.id_b = y.id_b
where x.id_b is not null
group by x.id_b, id_
)itx
) corr_itx
group by id_b
"""))